Stored Function

It’s similar to a function in programming languages:

• Always returns one value(via RETURN).
• Can only have IN parameters (no OUT or INOUT).
• Can be used inside SQL statements (like built-in functions: SUM(), NOW(), etc.).
• Encapsulates reusable logic.

Difference Between Stored Procedure and Stored Function

Feature	Stored Procedure	Stored Function
Return value	No direct return (can use OUT/INOUT params)	Always returns a single value
Called with	CALL procedure_name(...)	Used inside SQL (e.g., SELECT my_function(...))
Usage in queries	❌ Cannot be used in SELECT	✅ Can be used in SELECT, WHERE, etc.
Primary use case	Perform actions (insert, update, delete, complex queries)	Return a computed value

Differences Between Parameters in Procedures vs Functions

Feature	Stored Procedure	Stored Function
Parameter types	IN, OUT, INOUT	Only IN
Must return value?	❌ No (but can use OUT params)	✅ Yes (always returns 1 value)
Usage in SQL queries	❌ Cannot be used directly	✅ Can be used in SELECT, WHERE, etc.
Typical use cases	Complex operations (insert/update/delete, multiple results)	Calculations, transformations, validations

Syntax of Creating a Function

DELIMITER //

CREATE FUNCTION functionName(parameter_list)
RETURNS datatype
DETERMINISTIC
BEGIN
   -- Function body (must return a value)
   RETURN value;
END //

DELIMITER ;

• function_name → The name of the function.
• parameter_list → Input parameters (only IN type, unlike procedures).
• RETURNS datatype → Must specify the data type of the return value (e.g., INT, VARCHAR(50)).
• DETERMINISTIC → Tells MySQL that the function will always return the same output for the same input (important for replication & optimization).
• RETURN → Required to return a single value.

Simple Function (Square of a Number)

DELIMITER //

CREATE FUNCTION squareNumber(n INT)
RETURNS INT
DETERMINISTIC
BEGIN
   RETURN n * n;
END //

DELIMITER ;

To use this function

SELECT squareNumber(5) AS Result;

Suppose we have an employees table with first_name and last_name.

We can create a function to return the full name:

DELIMITER //

CREATE FUNCTION getFullName(fname VARCHAR(50), lname VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
   RETURN CONCAT(fname, ' ', lname);
END //

DELIMITER ;

To use this function:

SELECT getFullName(first_name, last_name) AS FullName
FROM employees;

Managing Functions

• Show all functions in a DB: SHOW FUNCTION STATUS WHERE Db = 'your_database_name';
• View function definition: SHOW CREATE FUNCTION CalculateAge;
• Delete a function: DROP FUNCTION CalculateAge;